﻿// -----------------------------------------------------------------------
// <copyright file="ProfileData.cs" company="VDC-IT" author="LTDUC">
//      Copyright (c) VDC-IT. All rights reserved.
// </copyright>
// -----------------------------------------------------------------------

namespace VDC.VONE.DAL
{
    using System;
    using System.Collections.Generic;
    using System.Data;
    using System.Data.SqlClient;
    using System.Reflection;
    using Microsoft.ApplicationBlocks.Data;
    using VDC.COMMON;
    using VDC.COMMON.NonPersistents;
    using VDC.VONE.ENTITIES;
    
    /// <summary>
    /// Class access to db Profile
    /// </summary>
    public class ProfileData
    {
        /// <summary>
        /// Get Profile By Id
        /// </summary>
        /// <param name="profileId">Param profileId</param>
        /// <returns>Return Profile</returns>
        public ProfileEN GetProfileById(int profileId)
        {
            string methodName = MethodBase.GetCurrentMethod().DeclaringType.ToString() + Constants.Dot + MethodBase.GetCurrentMethod().Name;
            LogHelper.Instance.WriteInfo(Constants.Begin + methodName);
            try
            {
                ProfileEN profile = null;
                var reader = SqlHelper.ExecuteReader(SqlHelper.ConnectionString, Constants.StorepProcedure.VOneProfileGetById, profileId);
                while (reader.Read())
                {
                    profile = new ProfileEN();

                    if (!reader.IsDBNull(0))
                    {
                        profile.ProfileId = reader.GetInt32(0);
                    }

                    if (!reader.IsDBNull(1))
                    {
                        profile.ProfileStatusId = reader.GetInt32(1);
                    }

                    if (!reader.IsDBNull(2))
                    {
                        profile.ProfileTypeId = reader.GetInt32(2);
                    }

                    if (!reader.IsDBNull(3))
                    {
                        profile.LevelId = reader.GetInt16(3);
                    }

                    if (!reader.IsDBNull(4))
                    {
                        profile.StorageLocationProfileListId = reader.GetInt32(4);
                    }

                    if (!reader.IsDBNull(5))
                    {
                        profile.CreateUserId = reader.GetInt32(5);
                    }

                    if (!reader.IsDBNull(6))
                    {
                        profile.Code = reader.GetString(6);
                    }

                    if (!reader.IsDBNull(7))
                    {
                        profile.Name = reader.GetString(7);
                    }

                    if (!reader.IsDBNull(8))
                    {
                        profile.SmsGet = reader.GetString(8);
                    }

                    if (!reader.IsDBNull(9))
                    {
                        profile.Email = reader.GetString(9);
                    }

                    if (!reader.IsDBNull(10))
                    {
                        profile.CreateDate = reader.GetDateTime(10);
                    }

                    if (!reader.IsDBNull(11))
                    {
                        profile.ReceivingDate = reader.GetDateTime(11);
                    }

                    if (!reader.IsDBNull(12))
                    {
                        profile.AppointmentDate = reader.GetDateTime(12);
                    }

                    if (!reader.IsDBNull(13))
                    {
                        profile.Object = reader.GetBoolean(13);
                    }

                    if (!reader.IsDBNull(14))
                    {
                        profile.SettlementDate = reader.GetDateTime(14);
                    }

                    if (!reader.IsDBNull(15))
                    {
                        profile.Notes = reader.GetString(15);
                    }

                    if (!reader.IsDBNull(16))
                    {
                        profile.Attachments = reader.GetString(16);
                    }

                    if (!reader.IsDBNull(17))
                    {
                        profile.ContentWork = reader.GetString(17);
                    }

                    if (!reader.IsDBNull(18))
                    {
                        profile.IsDelete = reader.GetBoolean(18);
                    }

                    if (!reader.IsDBNull(19))
                    {
                        profile.OrganName = reader.GetString(19);
                    }

                    if (!reader.IsDBNull(20))
                    {
                        profile.Address = reader.GetString(20);
                    }

                    if (!reader.IsDBNull(21))
                    {
                        profile.Phone = reader.GetString(21);
                    }

                    if (!reader.IsDBNull(22))
                    {
                        profile.AttachmentsOriginal = reader.GetString(22);
                    }

                    if (!reader.IsDBNull(23))
                    {
                        profile.IsOpen = reader.GetBoolean(23);
                    }
                }

                reader.Close();
                reader.Dispose();
                LogHelper.Instance.WriteInfo(Constants.End + methodName);
                return profile;
            }
            catch (SqlException ex)
            {
                LogHelper.Instance.WriteError(methodName + Constants.Colon + ex.Message);
                throw new Exception(ex.ToString());
            }
        }

        /// <summary>
        /// Get list all Profile
        /// </summary>
        /// <returns>Return list all Profile</returns>
        public IList<ProfileEN> GetProfiles()
        {
            string methodName = MethodBase.GetCurrentMethod().DeclaringType.ToString() + Constants.Dot + MethodBase.GetCurrentMethod().Name;
            LogHelper.Instance.WriteInfo(Constants.Begin + methodName);
            try
            {
                IList<ProfileEN> lstProfile = new List<ProfileEN>();
                SqlDataReader reader = SqlHelper.ExecuteReader(SqlHelper.ConnectionString, Constants.StorepProcedure.VOneProfileGetAll);
                while (reader.Read())
                {
                    ProfileEN profile = new ProfileEN();

                    if (!reader.IsDBNull(0))
                    {
                        profile.ProfileId = reader.GetInt32(0);
                    }

                    if (!reader.IsDBNull(1))
                    {
                        profile.ProfileStatusId = reader.GetInt32(1);
                    }

                    if (!reader.IsDBNull(2))
                    {
                        profile.ProfileTypeId = reader.GetInt32(2);
                    }

                    if (!reader.IsDBNull(3))
                    {
                        profile.LevelId = reader.GetInt16(3);
                    }

                    if (!reader.IsDBNull(4))
                    {
                        profile.StorageLocationProfileListId = reader.GetInt32(4);
                    }

                    if (!reader.IsDBNull(5))
                    {
                        profile.CreateUserId = reader.GetInt32(5);
                    }

                    if (!reader.IsDBNull(6))
                    {
                        profile.Code = reader.GetString(6);
                    }

                    if (!reader.IsDBNull(7))
                    {
                        profile.Name = reader.GetString(7);
                    }

                    if (!reader.IsDBNull(8))
                    {
                        profile.SmsGet = reader.GetString(8);
                    }

                    if (!reader.IsDBNull(9))
                    {
                        profile.Email = reader.GetString(9);
                    }

                    if (!reader.IsDBNull(10))
                    {
                        profile.CreateDate = reader.GetDateTime(10);
                    }

                    if (!reader.IsDBNull(11))
                    {
                        profile.ReceivingDate = reader.GetDateTime(11);
                    }

                    if (!reader.IsDBNull(12))
                    {
                        profile.AppointmentDate = reader.GetDateTime(12);
                    }

                    if (!reader.IsDBNull(13))
                    {
                        profile.Object = reader.GetBoolean(13);
                    }

                    if (!reader.IsDBNull(14))
                    {
                        profile.SettlementDate = reader.GetDateTime(14);
                    }

                    if (!reader.IsDBNull(15))
                    {
                        profile.Notes = reader.GetString(15);
                    }

                    if (!reader.IsDBNull(16))
                    {
                        profile.Attachments = reader.GetString(16);
                    }

                    if (!reader.IsDBNull(17))
                    {
                        profile.ContentWork = reader.GetString(17);
                    }

                    if (!reader.IsDBNull(18))
                    {
                        profile.IsDelete = reader.GetBoolean(18);
                    }

                    if (!reader.IsDBNull(19))
                    {
                        profile.OrganName = reader.GetString(19);
                    }

                    if (!reader.IsDBNull(20))
                    {
                        profile.Address = reader.GetString(20);
                    }

                    if (!reader.IsDBNull(21))
                    {
                        profile.Phone = reader.GetString(21);
                    }

                    if (!reader.IsDBNull(22))
                    {
                        profile.AttachmentsOriginal = reader.GetString(22);
                    }

                    if (!reader.IsDBNull(23))
                    {
                        profile.IsOpen = reader.GetBoolean(23);
                    }

                    lstProfile.Add(profile);
                }

                reader.Close();
                reader.Dispose();
                LogHelper.Instance.WriteInfo(Constants.End + methodName);
                return lstProfile;
            }
            catch (SqlException ex)
            {
                LogHelper.Instance.WriteError(methodName + Constants.Colon + ex.Message);
                throw new Exception(ex.ToString());
            }
        }

        /// <summary>
        /// Insert to db Profile
        /// </summary>
        /// <param name="profile">Paarm Profile</param>
        /// <returns>Insert Profile</returns>
        public int Insert(ProfileEN profile)
        {
            string methodName = MethodBase.GetCurrentMethod().DeclaringType.ToString() + Constants.Dot + MethodBase.GetCurrentMethod().Name;
            LogHelper.Instance.WriteInfo(Constants.Begin + methodName);
            try
            {
                var sqlParameters = new[]
                                {
                                    new SqlParameter(Constants.SqlParamerterName.Profile.ProfileStatusId, profile.ProfileStatusId),
                                    new SqlParameter(Constants.SqlParamerterName.Profile.ProfileTypeId, profile.ProfileTypeId),
                                    new SqlParameter(Constants.SqlParamerterName.Profile.LevelId, profile.LevelId),
                                    new SqlParameter(Constants.SqlParamerterName.Profile.StorageLocationProfileListId, profile.StorageLocationProfileListId),
                                    new SqlParameter(Constants.SqlParamerterName.Profile.CreateUserId, profile.CreateUserId),
                                    new SqlParameter(Constants.SqlParamerterName.Profile.Code, profile.Code),
                                    new SqlParameter(Constants.SqlParamerterName.Profile.Name, profile.Name),
                                    new SqlParameter(Constants.SqlParamerterName.Profile.SmsGet, profile.SmsGet),
                                    new SqlParameter(Constants.SqlParamerterName.Profile.Email, profile.Email),
                                    new SqlParameter(Constants.SqlParamerterName.Profile.CreateDate, (object)profile.CreateDate ?? DBNull.Value),
                                    new SqlParameter(Constants.SqlParamerterName.Profile.ReceivingDate, (object)profile.ReceivingDate ?? DBNull.Value),
                                    new SqlParameter(Constants.SqlParamerterName.Profile.AppointmentDate, (object)profile.AppointmentDate ?? DBNull.Value),
                                    new SqlParameter(Constants.SqlParamerterName.Profile.Object, profile.Object),
                                    new SqlParameter(Constants.SqlParamerterName.Profile.SettlementDate, (object)profile.SettlementDate ?? DBNull.Value),
                                    new SqlParameter(Constants.SqlParamerterName.Profile.Notes, profile.Notes),
                                    new SqlParameter(Constants.SqlParamerterName.Profile.Attachments, profile.Attachments),
                                    new SqlParameter(Constants.SqlParamerterName.Profile.ContentWork, profile.ContentWork),
                                    new SqlParameter(Constants.SqlParamerterName.Profile.IsDelete, profile.IsDelete),
                                    new SqlParameter(Constants.SqlParamerterName.Profile.OrganName, profile.OrganName),
                                    new SqlParameter(Constants.SqlParamerterName.Profile.Address, profile.Address),
                                    new SqlParameter(Constants.SqlParamerterName.Profile.Phone, profile.Phone),
                                    new SqlParameter(Constants.SqlParamerterName.Profile.AttachmentsOriginal, profile.AttachmentsOriginal),
                                    new SqlParameter(Constants.SqlParamerterName.Profile.IsOpen, profile.IsOpen)
                                };

                var result = Convert.ToInt32(SqlHelper.ExecuteScalar(SqlHelper.ConnectionString, Constants.StorepProcedure.VOneProfileInsert, sqlParameters));
                LogHelper.Instance.WriteInfo(Constants.End + methodName);
                return result;
            }
            catch (SqlException ex)
            {
                LogHelper.Instance.WriteError(methodName + Constants.Colon + ex.Message);
                throw new Exception(ex.ToString());
            }
        }

        /// <summary>
        /// Update to db Profile
        /// </summary>
        /// <param name="profile">Param Profile</param>
        public void Update(ProfileEN profile)
        {
            string methodName = MethodBase.GetCurrentMethod().DeclaringType.ToString() + Constants.Dot + MethodBase.GetCurrentMethod().Name;
            LogHelper.Instance.WriteInfo(Constants.Begin + methodName);
            try
            {
                var sqlParameters = new[]
                                {
                                    new SqlParameter(Constants.SqlParamerterName.Profile.ProfileId, profile.ProfileId),
                                    new SqlParameter(Constants.SqlParamerterName.Profile.ProfileStatusId, profile.ProfileStatusId),
                                    new SqlParameter(Constants.SqlParamerterName.Profile.ProfileTypeId, profile.ProfileTypeId),
                                    new SqlParameter(Constants.SqlParamerterName.Profile.LevelId, profile.LevelId),
                                    new SqlParameter(Constants.SqlParamerterName.Profile.StorageLocationProfileListId, profile.StorageLocationProfileListId),
                                    new SqlParameter(Constants.SqlParamerterName.Profile.CreateUserId, profile.CreateUserId),
                                    new SqlParameter(Constants.SqlParamerterName.Profile.Code, profile.Code),
                                    new SqlParameter(Constants.SqlParamerterName.Profile.Name, profile.Name),
                                    new SqlParameter(Constants.SqlParamerterName.Profile.SmsGet, profile.SmsGet),
                                    new SqlParameter(Constants.SqlParamerterName.Profile.Email, profile.Email),
                                    new SqlParameter(Constants.SqlParamerterName.Profile.CreateDate, (object)profile.CreateDate ?? DBNull.Value),
                                    new SqlParameter(Constants.SqlParamerterName.Profile.ReceivingDate, (object)profile.ReceivingDate ?? DBNull.Value),
                                    new SqlParameter(Constants.SqlParamerterName.Profile.AppointmentDate, (object)profile.AppointmentDate ?? DBNull.Value),
                                    new SqlParameter(Constants.SqlParamerterName.Profile.Object, profile.Object),
                                    new SqlParameter(Constants.SqlParamerterName.Profile.SettlementDate, (object)profile.SettlementDate ?? DBNull.Value),
                                    new SqlParameter(Constants.SqlParamerterName.Profile.Notes, profile.Notes),
                                    new SqlParameter(Constants.SqlParamerterName.Profile.Attachments, profile.Attachments),
                                    new SqlParameter(Constants.SqlParamerterName.Profile.ContentWork, profile.ContentWork),
                                    new SqlParameter(Constants.SqlParamerterName.Profile.IsDelete, profile.IsDelete),
                                    new SqlParameter(Constants.SqlParamerterName.Profile.OrganName, profile.OrganName),
                                    new SqlParameter(Constants.SqlParamerterName.Profile.Address, profile.Address),
                                    new SqlParameter(Constants.SqlParamerterName.Profile.Phone, profile.Phone),
                                    new SqlParameter(Constants.SqlParamerterName.Profile.AttachmentsOriginal, profile.AttachmentsOriginal),
                                    new SqlParameter(Constants.SqlParamerterName.Profile.IsOpen, profile.IsOpen)
                                };

                SqlHelper.ExecuteNonQuery(SqlHelper.ConnectionString, Constants.StorepProcedure.VOneProfileUpdate, sqlParameters);
                LogHelper.Instance.WriteInfo(Constants.End + methodName);
            }
            catch (SqlException ex)
            {
                LogHelper.Instance.WriteError(methodName + Constants.Colon + ex.Message);
                throw new Exception(ex.ToString());
            }
        }

        /// <summary>
        /// Delete Profile by profileId
        /// </summary>
        /// <param name="profileId">param profileId</param>
        public void Delete(int profileId)
        {
            string methodName = MethodBase.GetCurrentMethod().DeclaringType.ToString() + Constants.Dot + MethodBase.GetCurrentMethod().Name;
            LogHelper.Instance.WriteInfo(Constants.Begin + methodName);
            try
            {
                var sqlParameters = new[] 
                { 
                    new SqlParameter(Constants.SqlParamerterName.Profile.ProfileId, profileId)
                };

                SqlHelper.ExecuteNonQuery(SqlHelper.ConnectionString, Constants.StorepProcedure.VOneProfileDelete, sqlParameters);
                LogHelper.Instance.WriteInfo(Constants.End + methodName);
            }
            catch (SqlException ex)
            {
                LogHelper.Instance.WriteError(methodName + Constants.Colon + ex.Message);
                throw new Exception(ex.ToString());
            }
        }

        /// <summary>
        /// Get List Profile By ProfileStatusId
        /// </summary>
        /// <param name="profileStatusId">Param profileStatusId</param>
        /// <returns>Return List Profile</returns>
        public IList<ProfileEN> GetProfileByProfileStatusId(int profileStatusId)
        {
            string methodName = MethodBase.GetCurrentMethod().DeclaringType.ToString() + Constants.Dot + MethodBase.GetCurrentMethod().Name;
            LogHelper.Instance.WriteInfo(Constants.Begin + methodName);
            try
            {
                IList<ProfileEN> lstProfile = new List<ProfileEN>();
                var reader = SqlHelper.ExecuteReader(SqlHelper.ConnectionString, Constants.StorepProcedure.VOneProfileGetByProfileStatusId, profileStatusId);
                while (reader.Read())
                {
                    ProfileEN profile = new ProfileEN();

                    if (!reader.IsDBNull(0))
                    {
                        profile.ProfileId = reader.GetInt32(0);
                    }

                    if (!reader.IsDBNull(1))
                    {
                        profile.ProfileStatusId = reader.GetInt32(1);
                    }

                    if (!reader.IsDBNull(2))
                    {
                        profile.ProfileTypeId = reader.GetInt32(2);
                    }

                    if (!reader.IsDBNull(3))
                    {
                        profile.LevelId = reader.GetInt16(3);
                    }

                    if (!reader.IsDBNull(4))
                    {
                        profile.StorageLocationProfileListId = reader.GetInt32(4);
                    }

                    if (!reader.IsDBNull(5))
                    {
                        profile.CreateUserId = reader.GetInt32(5);
                    }

                    if (!reader.IsDBNull(6))
                    {
                        profile.Code = reader.GetString(6);
                    }

                    if (!reader.IsDBNull(7))
                    {
                        profile.Name = reader.GetString(7);
                    }

                    if (!reader.IsDBNull(8))
                    {
                        profile.SmsGet = reader.GetString(8);
                    }

                    if (!reader.IsDBNull(9))
                    {
                        profile.Email = reader.GetString(9);
                    }

                    if (!reader.IsDBNull(10))
                    {
                        profile.CreateDate = reader.GetDateTime(10);
                    }

                    if (!reader.IsDBNull(11))
                    {
                        profile.ReceivingDate = reader.GetDateTime(11);
                    }

                    if (!reader.IsDBNull(12))
                    {
                        profile.AppointmentDate = reader.GetDateTime(12);
                    }

                    if (!reader.IsDBNull(13))
                    {
                        profile.Object = reader.GetBoolean(13);
                    }

                    if (!reader.IsDBNull(14))
                    {
                        profile.SettlementDate = reader.GetDateTime(14);
                    }

                    if (!reader.IsDBNull(15))
                    {
                        profile.Notes = reader.GetString(15);
                    }

                    if (!reader.IsDBNull(16))
                    {
                        profile.Attachments = reader.GetString(16);
                    }

                    if (!reader.IsDBNull(17))
                    {
                        profile.ContentWork = reader.GetString(17);
                    }

                    if (!reader.IsDBNull(18))
                    {
                        profile.IsDelete = reader.GetBoolean(18);
                    }

                    if (!reader.IsDBNull(19))
                    {
                        profile.OrganName = reader.GetString(19);
                    }

                    if (!reader.IsDBNull(20))
                    {
                        profile.Address = reader.GetString(20);
                    }

                    if (!reader.IsDBNull(21))
                    {
                        profile.Phone = reader.GetString(21);
                    }

                    if (!reader.IsDBNull(22))
                    {
                        profile.AttachmentsOriginal = reader.GetString(22);
                    }

                    if (!reader.IsDBNull(23))
                    {
                        profile.IsOpen = reader.GetBoolean(23);
                    }

                    lstProfile.Add(profile);
                }

                reader.Close();
                reader.Dispose();
                LogHelper.Instance.WriteInfo(Constants.End + methodName);
                return lstProfile;
            }
            catch (SqlException ex)
            {
                LogHelper.Instance.WriteError(methodName + Constants.Colon + ex.Message);
                throw new Exception(ex.ToString());
            }
        }

        /// <summary>
        /// Get List Profile By StorageLocationProfileListId
        /// </summary>
        /// <param name="storageLocationProfileListId">Param StorageLocationProfileListId</param>
        /// <returns>Return List Profile</returns>
        public IList<ProfileEN> GetProfileByStorageLocationProfileListId(int storageLocationProfileListId)
        {
            string methodName = MethodBase.GetCurrentMethod().DeclaringType.ToString() + Constants.Dot + MethodBase.GetCurrentMethod().Name;
            LogHelper.Instance.WriteInfo(Constants.Begin + methodName);
            try
            {
                IList<ProfileEN> lstProfile = new List<ProfileEN>();
                var reader = SqlHelper.ExecuteReader(SqlHelper.ConnectionString, Constants.StorepProcedure.VOneProfileGetByStorageLocationProfileListId, storageLocationProfileListId);
                while (reader.Read())
                {
                    ProfileEN profile = new ProfileEN();

                    if (!reader.IsDBNull(0))
                    {
                        profile.ProfileId = reader.GetInt32(0);
                    }

                    if (!reader.IsDBNull(1))
                    {
                        profile.ProfileStatusId = reader.GetInt32(1);
                    }

                    if (!reader.IsDBNull(2))
                    {
                        profile.ProfileTypeId = reader.GetInt32(2);
                    }

                    if (!reader.IsDBNull(3))
                    {
                        profile.LevelId = reader.GetInt16(3);
                    }

                    if (!reader.IsDBNull(4))
                    {
                        profile.StorageLocationProfileListId = reader.GetInt32(4);
                    }

                    if (!reader.IsDBNull(5))
                    {
                        profile.CreateUserId = reader.GetInt32(5);
                    }

                    if (!reader.IsDBNull(6))
                    {
                        profile.Code = reader.GetString(6);
                    }

                    if (!reader.IsDBNull(7))
                    {
                        profile.Name = reader.GetString(7);
                    }

                    if (!reader.IsDBNull(8))
                    {
                        profile.SmsGet = reader.GetString(8);
                    }

                    if (!reader.IsDBNull(9))
                    {
                        profile.Email = reader.GetString(9);
                    }

                    if (!reader.IsDBNull(10))
                    {
                        profile.CreateDate = reader.GetDateTime(10);
                    }

                    if (!reader.IsDBNull(11))
                    {
                        profile.ReceivingDate = reader.GetDateTime(11);
                    }

                    if (!reader.IsDBNull(12))
                    {
                        profile.AppointmentDate = reader.GetDateTime(12);
                    }

                    if (!reader.IsDBNull(13))
                    {
                        profile.Object = reader.GetBoolean(13);
                    }

                    if (!reader.IsDBNull(14))
                    {
                        profile.SettlementDate = reader.GetDateTime(14);
                    }

                    if (!reader.IsDBNull(15))
                    {
                        profile.Notes = reader.GetString(15);
                    }

                    if (!reader.IsDBNull(16))
                    {
                        profile.Attachments = reader.GetString(16);
                    }

                    if (!reader.IsDBNull(17))
                    {
                        profile.ContentWork = reader.GetString(17);
                    }

                    if (!reader.IsDBNull(18))
                    {
                        profile.IsDelete = reader.GetBoolean(18);
                    }

                    if (!reader.IsDBNull(19))
                    {
                        profile.OrganName = reader.GetString(19);
                    }

                    if (!reader.IsDBNull(20))
                    {
                        profile.Address = reader.GetString(20);
                    }

                    if (!reader.IsDBNull(21))
                    {
                        profile.Phone = reader.GetString(21);
                    }

                    if (!reader.IsDBNull(22))
                    {
                        profile.AttachmentsOriginal = reader.GetString(22);
                    }

                    if (!reader.IsDBNull(23))
                    {
                        profile.IsOpen = reader.GetBoolean(23);
                    }

                    lstProfile.Add(profile);
                }

                reader.Close();
                reader.Dispose();
                LogHelper.Instance.WriteInfo(Constants.End + methodName);
                return lstProfile;
            }
            catch (SqlException ex)
            {
                LogHelper.Instance.WriteError(methodName + Constants.Colon + ex.Message);
                throw new Exception(ex.ToString());
            }
        }

        /// <summary>
        /// Get List Profile By LevelId
        /// </summary>
        /// <param name="levelId">Param levelId</param>
        /// <returns>Return List Profile</returns>
        public IList<ProfileEN> GetProfileByLevelId(int levelId)
        {
            string methodName = MethodBase.GetCurrentMethod().DeclaringType.ToString() + Constants.Dot + MethodBase.GetCurrentMethod().Name;
            LogHelper.Instance.WriteInfo(Constants.Begin + methodName);
            try
            {
                IList<ProfileEN> lstProfile = new List<ProfileEN>();
                var reader = SqlHelper.ExecuteReader(SqlHelper.ConnectionString, Constants.StorepProcedure.VOneProfileGetByLevelId, levelId);
                while (reader.Read())
                {
                    ProfileEN profile = new ProfileEN();

                    if (!reader.IsDBNull(0))
                    {
                        profile.ProfileId = reader.GetInt32(0);
                    }

                    if (!reader.IsDBNull(1))
                    {
                        profile.ProfileStatusId = reader.GetInt32(1);
                    }

                    if (!reader.IsDBNull(2))
                    {
                        profile.ProfileTypeId = reader.GetInt32(2);
                    }

                    if (!reader.IsDBNull(3))
                    {
                        profile.LevelId = reader.GetInt16(3);
                    }

                    if (!reader.IsDBNull(4))
                    {
                        profile.StorageLocationProfileListId = reader.GetInt32(4);
                    }

                    if (!reader.IsDBNull(5))
                    {
                        profile.CreateUserId = reader.GetInt32(5);
                    }

                    if (!reader.IsDBNull(6))
                    {
                        profile.Code = reader.GetString(6);
                    }

                    if (!reader.IsDBNull(7))
                    {
                        profile.Name = reader.GetString(7);
                    }

                    if (!reader.IsDBNull(8))
                    {
                        profile.SmsGet = reader.GetString(8);
                    }

                    if (!reader.IsDBNull(9))
                    {
                        profile.Email = reader.GetString(9);
                    }

                    if (!reader.IsDBNull(10))
                    {
                        profile.CreateDate = reader.GetDateTime(10);
                    }

                    if (!reader.IsDBNull(11))
                    {
                        profile.ReceivingDate = reader.GetDateTime(11);
                    }

                    if (!reader.IsDBNull(12))
                    {
                        profile.AppointmentDate = reader.GetDateTime(12);
                    }

                    if (!reader.IsDBNull(13))
                    {
                        profile.Object = reader.GetBoolean(13);
                    }

                    if (!reader.IsDBNull(14))
                    {
                        profile.SettlementDate = reader.GetDateTime(14);
                    }

                    if (!reader.IsDBNull(15))
                    {
                        profile.Notes = reader.GetString(15);
                    }

                    if (!reader.IsDBNull(16))
                    {
                        profile.Attachments = reader.GetString(16);
                    }

                    if (!reader.IsDBNull(17))
                    {
                        profile.ContentWork = reader.GetString(17);
                    }

                    if (!reader.IsDBNull(18))
                    {
                        profile.IsDelete = reader.GetBoolean(18);
                    }

                    if (!reader.IsDBNull(19))
                    {
                        profile.OrganName = reader.GetString(19);
                    }

                    if (!reader.IsDBNull(20))
                    {
                        profile.Address = reader.GetString(20);
                    }

                    if (!reader.IsDBNull(21))
                    {
                        profile.Phone = reader.GetString(21);
                    }

                    if (!reader.IsDBNull(22))
                    {
                        profile.AttachmentsOriginal = reader.GetString(22);
                    }

                    if (!reader.IsDBNull(23))
                    {
                        profile.IsOpen = reader.GetBoolean(23);
                    }

                    lstProfile.Add(profile);
                }

                reader.Close();
                reader.Dispose();
                LogHelper.Instance.WriteInfo(Constants.End + methodName);
                return lstProfile;
            }
            catch (SqlException ex)
            {
                LogHelper.Instance.WriteError(methodName + Constants.Colon + ex.Message);
                throw new Exception(ex.ToString());
            }
        }

        /// <summary>
        /// Gets the profile by status and user.
        /// </summary>
        /// <param name="profileStatusCode">The profile status enum.</param>
        /// <param name="userId">The user id.</param>
        /// <param name="code">The profile status enum.</param>
        /// <param name="name">The user id.</param>
        /// <returns>Return List Profile</returns>
        public IList<ProfileEN> GetProfileByStatusAndUser(string profileStatusCode, int userId, string code, string name)
        {
            string methodName = MethodBase.GetCurrentMethod().DeclaringType.ToString() + Constants.Dot + MethodBase.GetCurrentMethod().Name;
            LogHelper.Instance.WriteInfo(Constants.Begin + methodName);
            try
            {
                var newCode = !string.IsNullOrWhiteSpace(code) ? code.Trim() : (object) DBNull.Value;
                var newName = !string.IsNullOrWhiteSpace(name) ? name.Trim() : (object) DBNull.Value;
                IList<ProfileEN> lstProfile = new List<ProfileEN>();
                var reader = SqlHelper.ExecuteReader(SqlHelper.ConnectionString, Constants.StorepProcedure.VOneProfileGetByStatusUser, profileStatusCode, userId, newCode, newName);
                while (reader.Read())
                {
                    ProfileEN profile = new ProfileEN();

                    if (!reader.IsDBNull(0))
                    {
                        profile.ProfileId = reader.GetInt32(0);
                    }

                    if (!reader.IsDBNull(1))
                    {
                        profile.ProfileStatusId = reader.GetInt32(1);
                    }

                    if (!reader.IsDBNull(2))
                    {
                        profile.ProfileTypeId = reader.GetInt32(2);
                    }

                    if (!reader.IsDBNull(3))
                    {
                        profile.LevelId = reader.GetInt16(3);
                    }

                    if (!reader.IsDBNull(4))
                    {
                        profile.StorageLocationProfileListId = reader.GetInt32(4);
                    }

                    if (!reader.IsDBNull(5))
                    {
                        profile.CreateUserId = reader.GetInt32(5);
                    }

                    if (!reader.IsDBNull(6))
                    {
                        profile.Code = reader.GetString(6);
                    }

                    if (!reader.IsDBNull(7))
                    {
                        profile.Name = reader.GetString(7);
                    }

                    if (!reader.IsDBNull(8))
                    {
                        profile.SmsGet = reader.GetString(8);
                    }

                    if (!reader.IsDBNull(9))
                    {
                        profile.Email = reader.GetString(9);
                    }

                    if (!reader.IsDBNull(10))
                    {
                        profile.CreateDate = reader.GetDateTime(10);
                    }

                    if (!reader.IsDBNull(11))
                    {
                        profile.ReceivingDate = reader.GetDateTime(11);
                    }

                    if (!reader.IsDBNull(12))
                    {
                        profile.AppointmentDate = reader.GetDateTime(12);
                    }

                    if (!reader.IsDBNull(13))
                    {
                        profile.Object = reader.GetBoolean(13);
                    }

                    if (!reader.IsDBNull(14))
                    {
                        profile.SettlementDate = reader.GetDateTime(14);
                    }

                    if (!reader.IsDBNull(15))
                    {
                        profile.Notes = reader.GetString(15);
                    }

                    if (!reader.IsDBNull(16))
                    {
                        profile.Attachments = reader.GetString(16);
                    }

                    if (!reader.IsDBNull(17))
                    {
                        profile.ContentWork = reader.GetString(17);
                    }

                    if (!reader.IsDBNull(18))
                    {
                        profile.IsDelete = reader.GetBoolean(18);
                    }

                    if (!reader.IsDBNull(19))
                    {
                        profile.OrganName = reader.GetString(19);
                    }

                    if (!reader.IsDBNull(20))
                    {
                        profile.Address = reader.GetString(20);
                    }

                    if (!reader.IsDBNull(21))
                    {
                        profile.Phone = reader.GetString(21);
                    }

                    if (!reader.IsDBNull(22))
                    {
                        profile.AttachmentsOriginal = reader.GetString(22);
                    }

                    if (!reader.IsDBNull(23))
                    {
                        profile.IsOpen = reader.GetBoolean(23);
                    }

                    lstProfile.Add(profile);
                }

                reader.Close();
                reader.Dispose();
                LogHelper.Instance.WriteInfo(Constants.End + methodName);
                return lstProfile;
            }
            catch (SqlException ex)
            {
                LogHelper.Instance.WriteError(methodName + Constants.Colon + ex.Message);
                throw new Exception(ex.ToString());
            }
        }

        /// <summary>
        /// Gets the newest profile.
        /// </summary>
        /// <returns>return ProfileEN</returns>
        public ProfileEN GetNewestProfile()
        {
            string methodName = MethodBase.GetCurrentMethod().DeclaringType.ToString() + Constants.Dot + MethodBase.GetCurrentMethod().Name;
            LogHelper.Instance.WriteInfo(Constants.Begin + methodName);
            try
            {
                ProfileEN profile = null;
                var reader = SqlHelper.ExecuteReader(SqlHelper.ConnectionString, Constants.StorepProcedure.VOneProfileGetNewest);
                while (reader.Read())
                {
                    profile = new ProfileEN();

                    if (!reader.IsDBNull(0))
                    {
                        profile.ProfileId = reader.GetInt32(0);
                    }

                    if (!reader.IsDBNull(1))
                    {
                        profile.ProfileStatusId = reader.GetInt32(1);
                    }

                    if (!reader.IsDBNull(2))
                    {
                        profile.ProfileTypeId = reader.GetInt32(2);
                    }

                    if (!reader.IsDBNull(3))
                    {
                        profile.LevelId = reader.GetInt16(3);
                    }

                    if (!reader.IsDBNull(4))
                    {
                        profile.StorageLocationProfileListId = reader.GetInt32(4);
                    }

                    if (!reader.IsDBNull(5))
                    {
                        profile.CreateUserId = reader.GetInt32(5);
                    }

                    if (!reader.IsDBNull(6))
                    {
                        profile.Code = reader.GetString(6);
                    }

                    if (!reader.IsDBNull(7))
                    {
                        profile.Name = reader.GetString(7);
                    }

                    if (!reader.IsDBNull(8))
                    {
                        profile.SmsGet = reader.GetString(8);
                    }

                    if (!reader.IsDBNull(9))
                    {
                        profile.Email = reader.GetString(9);
                    }

                    if (!reader.IsDBNull(10))
                    {
                        profile.CreateDate = reader.GetDateTime(10);
                    }

                    if (!reader.IsDBNull(11))
                    {
                        profile.ReceivingDate = reader.GetDateTime(11);
                    }

                    if (!reader.IsDBNull(12))
                    {
                        profile.AppointmentDate = reader.GetDateTime(12);
                    }

                    if (!reader.IsDBNull(13))
                    {
                        profile.Object = reader.GetBoolean(13);
                    }

                    if (!reader.IsDBNull(14))
                    {
                        profile.SettlementDate = reader.GetDateTime(14);
                    }

                    if (!reader.IsDBNull(15))
                    {
                        profile.Notes = reader.GetString(15);
                    }

                    if (!reader.IsDBNull(16))
                    {
                        profile.Attachments = reader.GetString(16);
                    }

                    if (!reader.IsDBNull(17))
                    {
                        profile.ContentWork = reader.GetString(17);
                    }

                    if (!reader.IsDBNull(18))
                    {
                        profile.IsDelete = reader.GetBoolean(18);
                    }

                    if (!reader.IsDBNull(19))
                    {
                        profile.OrganName = reader.GetString(19);
                    }

                    if (!reader.IsDBNull(20))
                    {
                        profile.Address = reader.GetString(20);
                    }

                    if (!reader.IsDBNull(21))
                    {
                        profile.Phone = reader.GetString(21);
                    }

                    if (!reader.IsDBNull(22))
                    {
                        profile.AttachmentsOriginal = reader.GetString(22);
                    }

                    if (!reader.IsDBNull(23))
                    {
                        profile.IsOpen = reader.GetBoolean(23);
                    }
                }

                reader.Close();
                reader.Dispose();
                LogHelper.Instance.WriteInfo(Constants.End + methodName);
                return profile;
            }
            catch (SqlException ex)
            {
                LogHelper.Instance.WriteError(methodName + Constants.Colon + ex.Message);
                throw new Exception(ex.ToString());
            }
        }

        /// <summary>
        /// Search Profile
        /// </summary>
        /// <param name="searchProfileNP">param searchProfileNP</param>
        /// <returns>Return result search</returns>
        public IList<ProfileEN> GetProfileSearch(SearchProfileNP searchProfileNP)
        {
            string methodName = MethodBase.GetCurrentMethod().DeclaringType.ToString() + Constants.Dot + MethodBase.GetCurrentMethod().Name;
            LogHelper.Instance.WriteInfo(Constants.Begin + methodName);
            try
            {
                var code = searchProfileNP.Code;
                var name = searchProfileNP.Name;
                var sqlParameters = new[]
                                {
                                    new SqlParameter(Constants.SqlParamerterName.Profile.Code, !string.IsNullOrWhiteSpace(code) ? code.Trim() : (object)DBNull.Value),
                                    new SqlParameter(Constants.SqlParamerterName.Profile.Name, !string.IsNullOrWhiteSpace(name) ? name.Trim() : (object)DBNull.Value),
                                    new SqlParameter(Constants.SqlParamerterName.Profile.ProfileStatusCode, searchProfileNP.ProfileStatusCode),
                                    new SqlParameter(Constants.SqlParamerterName.Profile.ProfileTypeId, searchProfileNP.ProfileTypeId),
                                    new SqlParameter(Constants.SqlParamerterName.Profile.ReceivingFromDate, searchProfileNP.ReceivingFromDate),
                                    new SqlParameter(Constants.SqlParamerterName.Profile.ReceivingToDate, searchProfileNP.ReceivingToDate),
                                    new SqlParameter(Constants.SqlParamerterName.Profile.AppointmentFromDate, searchProfileNP.AppointmentFromDate),
                                    new SqlParameter(Constants.SqlParamerterName.Profile.AppointmentToDate, searchProfileNP.AppointmentToDate),
                                    new SqlParameter(Constants.SqlParamerterName.Profile.PageIndex, searchProfileNP.PageIndex),
                                    new SqlParameter(Constants.SqlParamerterName.Profile.PageSize, searchProfileNP.PageSize),
                                    new SqlParameter(Constants.SqlParamerterName.Profile.TotalRecords, searchProfileNP.TotalRecords)
                                };

                sqlParameters[10].Direction = ParameterDirection.Output;
                
                IList<ProfileEN> lstProfile = new List<ProfileEN>();
                var reader = SqlHelper.ExecuteReader(SqlHelper.ConnectionString, Constants.StorepProcedure.VOneProfileSearch, sqlParameters);
                while (reader.Read())
                {
                    ProfileEN profile = new ProfileEN();

                    if (!reader.IsDBNull(0))
                    {
                        profile.ProfileId = reader.GetInt32(0);
                    }

                    if (!reader.IsDBNull(1))
                    {
                        profile.ProfileStatusId = reader.GetInt32(1);
                    }

                    if (!reader.IsDBNull(2))
                    {
                        profile.ProfileTypeId = reader.GetInt32(2);
                    }

                    if (!reader.IsDBNull(3))
                    {
                        profile.LevelId = reader.GetInt16(3);
                    }

                    if (!reader.IsDBNull(4))
                    {
                        profile.StorageLocationProfileListId = reader.GetInt32(4);
                    }

                    if (!reader.IsDBNull(5))
                    {
                        profile.CreateUserId = reader.GetInt32(5);
                    }

                    if (!reader.IsDBNull(6))
                    {
                        profile.Code = reader.GetString(6);
                    }

                    if (!reader.IsDBNull(7))
                    {
                        profile.Name = reader.GetString(7);
                    }

                    if (!reader.IsDBNull(8))
                    {
                        profile.SmsGet = reader.GetString(8);
                    }

                    if (!reader.IsDBNull(9))
                    {
                        profile.Email = reader.GetString(9);
                    }

                    if (!reader.IsDBNull(10))
                    {
                        profile.CreateDate = reader.GetDateTime(10);
                    }

                    if (!reader.IsDBNull(11))
                    {
                        profile.ReceivingDate = reader.GetDateTime(11);
                    }

                    if (!reader.IsDBNull(12))
                    {
                        profile.AppointmentDate = reader.GetDateTime(12);
                    }

                    if (!reader.IsDBNull(13))
                    {
                        profile.Object = reader.GetBoolean(13);
                    }

                    if (!reader.IsDBNull(14))
                    {
                        profile.SettlementDate = reader.GetDateTime(14);
                    }

                    if (!reader.IsDBNull(15))
                    {
                        profile.Notes = reader.GetString(15);
                    }

                    if (!reader.IsDBNull(16))
                    {
                        profile.Attachments = reader.GetString(16);
                    }

                    if (!reader.IsDBNull(17))
                    {
                        profile.ContentWork = reader.GetString(17);
                    }

                    if (!reader.IsDBNull(18))
                    {
                        profile.IsDelete = reader.GetBoolean(18);
                    }

                    if (!reader.IsDBNull(19))
                    {
                        profile.OrganName = reader.GetString(19);
                    }

                    if (!reader.IsDBNull(20))
                    {
                        profile.Address = reader.GetString(20);
                    }

                    if (!reader.IsDBNull(21))
                    {
                        profile.Phone = reader.GetString(21);
                    }

                    if (!reader.IsDBNull(22))
                    {
                        profile.AttachmentsOriginal = reader.GetString(22);
                    }

                    if (!reader.IsDBNull(23))
                    {
                        profile.IsOpen = reader.GetBoolean(23);
                    }

                    lstProfile.Add(profile);
                }

                reader.Close();
                searchProfileNP.TotalRecords = Convert.ToInt32(sqlParameters[10].Value);
                reader.Dispose();
                LogHelper.Instance.WriteInfo(Constants.End + methodName);
                return lstProfile;
            }
            catch (SqlException ex)
            {
                LogHelper.Instance.WriteError(methodName + Constants.Colon + ex.Message);
                throw new Exception(ex.ToString());
            }
        }

        /// <summary>
        /// Search Profile
        /// </summary>
        /// <param name="searchProfileNP">param searchProfileNP</param>
        /// <returns>Return List Profile By String Ids</returns>
        public IList<ProfileEN> GetProfileByIds(SearchProfileNP searchProfileNP)
        {
            string methodName = MethodBase.GetCurrentMethod().DeclaringType.ToString() + Constants.Dot + MethodBase.GetCurrentMethod().Name;
            LogHelper.Instance.WriteInfo(Constants.Begin + methodName);
            try
            {
                var sqlParameters = new[]
                                {
                                    new SqlParameter(Constants.SqlParamerterName.Profile.Ids, searchProfileNP.Ids),
                                    new SqlParameter(Constants.SqlParamerterName.Profile.Delimeter, searchProfileNP.Delimeter),
                                    new SqlParameter(Constants.SqlParamerterName.Profile.PageIndex, searchProfileNP.PageIndex),
                                    new SqlParameter(Constants.SqlParamerterName.Profile.PageSize, searchProfileNP.PageSize),
                                    new SqlParameter(Constants.SqlParamerterName.Profile.TotalRecords, searchProfileNP.TotalRecords)
                                };

                sqlParameters[4].Direction = ParameterDirection.Output;

                IList<ProfileEN> lstProfile = new List<ProfileEN>();
                var reader = SqlHelper.ExecuteReader(SqlHelper.ConnectionString, "[VONE_Profile_Get_By_Ids]", sqlParameters);
                while (reader.Read())
                {
                    ProfileEN profile = new ProfileEN();

                    if (!reader.IsDBNull(0))
                    {
                        profile.ProfileId = reader.GetInt32(0);
                    }

                    if (!reader.IsDBNull(1))
                    {
                        profile.ProfileStatusId = reader.GetInt32(1);
                    }

                    if (!reader.IsDBNull(2))
                    {
                        profile.ProfileTypeId = reader.GetInt32(2);
                    }

                    if (!reader.IsDBNull(3))
                    {
                        profile.LevelId = reader.GetInt16(3);
                    }

                    if (!reader.IsDBNull(4))
                    {
                        profile.StorageLocationProfileListId = reader.GetInt32(4);
                    }

                    if (!reader.IsDBNull(5))
                    {
                        profile.CreateUserId = reader.GetInt32(5);
                    }

                    if (!reader.IsDBNull(6))
                    {
                        profile.Code = reader.GetString(6);
                    }

                    if (!reader.IsDBNull(7))
                    {
                        profile.Name = reader.GetString(7);
                    }

                    if (!reader.IsDBNull(8))
                    {
                        profile.SmsGet = reader.GetString(8);
                    }

                    if (!reader.IsDBNull(9))
                    {
                        profile.Email = reader.GetString(9);
                    }

                    if (!reader.IsDBNull(10))
                    {
                        profile.CreateDate = reader.GetDateTime(10);
                    }

                    if (!reader.IsDBNull(11))
                    {
                        profile.ReceivingDate = reader.GetDateTime(11);
                    }

                    if (!reader.IsDBNull(12))
                    {
                        profile.AppointmentDate = reader.GetDateTime(12);
                    }

                    if (!reader.IsDBNull(13))
                    {
                        profile.Object = reader.GetBoolean(13);
                    }

                    if (!reader.IsDBNull(14))
                    {
                        profile.SettlementDate = reader.GetDateTime(14);
                    }

                    if (!reader.IsDBNull(15))
                    {
                        profile.Notes = reader.GetString(15);
                    }

                    if (!reader.IsDBNull(16))
                    {
                        profile.Attachments = reader.GetString(16);
                    }

                    if (!reader.IsDBNull(17))
                    {
                        profile.ContentWork = reader.GetString(17);
                    }

                    if (!reader.IsDBNull(18))
                    {
                        profile.IsDelete = reader.GetBoolean(18);
                    }

                    if (!reader.IsDBNull(19))
                    {
                        profile.OrganName = reader.GetString(19);
                    }

                    if (!reader.IsDBNull(20))
                    {
                        profile.Address = reader.GetString(20);
                    }

                    if (!reader.IsDBNull(21))
                    {
                        profile.Phone = reader.GetString(21);
                    }

                    if (!reader.IsDBNull(22))
                    {
                        profile.AttachmentsOriginal = reader.GetString(22);
                    }

                    if (!reader.IsDBNull(23))
                    {
                        profile.IsOpen = reader.GetBoolean(23);
                    }

                    lstProfile.Add(profile);
                }

                reader.Close();
                searchProfileNP.TotalRecords = Convert.ToInt32(sqlParameters[4].Value);
                reader.Dispose();
                LogHelper.Instance.WriteInfo(Constants.End + methodName);
                return lstProfile;
            }
            catch (SqlException ex)
            {
                LogHelper.Instance.WriteError(methodName + Constants.Colon + ex.Message);
                throw new Exception(ex.ToString());
            }
        }

        /// <summary>
        /// Return data statistical analysis
        /// </summary>
        /// <param name="searchProfileNP">param searchProfileNP</param>
        /// <returns>return data</returns>
        public DataTable GetProfileStatisticalAnalysis(SearchProfileNP searchProfileNP)
        {
            string methodName = MethodBase.GetCurrentMethod().DeclaringType.ToString() + Constants.Dot + MethodBase.GetCurrentMethod().Name;
            LogHelper.Instance.WriteInfo(Constants.Begin + methodName);
            try
            {
                var sqlParameters = new[]
                                {
                                    new SqlParameter(Constants.SqlParamerterName.Profile.ProfileTypeId, searchProfileNP.ProfileTypeId),
                                    new SqlParameter(Constants.SqlParamerterName.Profile.ReceivingFromDate, searchProfileNP.ReceivingFromDate),
                                    new SqlParameter(Constants.SqlParamerterName.Profile.ReceivingToDate, searchProfileNP.ReceivingToDate),
                                };

                var dataSet = SqlHelper.ExecuteDataset(SqlHelper.ConnectionString, Constants.StorepProcedure.VOneProfileStatisticalAnalysis, sqlParameters);
                
                LogHelper.Instance.WriteInfo(Constants.End + methodName);
                return dataSet.Tables.Count > 0 ? dataSet.Tables[0] : null;
            }
            catch (SqlException ex)
            {
                LogHelper.Instance.WriteError(methodName + Constants.Colon + ex.Message);
                throw new Exception(ex.ToString());
            }
        }

        public DataTable GetProfileStatisticalAnalysisLevelDepartment(SearchProfileNP searchProfileNP)
        {
            string methodName = MethodBase.GetCurrentMethod().DeclaringType.ToString() + Constants.Dot + MethodBase.GetCurrentMethod().Name;
            LogHelper.Instance.WriteInfo(Constants.Begin + methodName);
            try
            {
                var sqlParameters = new[]
                                {
                                    new SqlParameter(Constants.SqlParamerterName.SearchProfileNP.DepartmentId, searchProfileNP.DepartmentId),
                                    new SqlParameter(Constants.SqlParamerterName.SearchProfileNP.ReceivingFromDate, searchProfileNP.ReceivingFromDate),
                                    new SqlParameter(Constants.SqlParamerterName.SearchProfileNP.ReceivingToDate, searchProfileNP.ReceivingToDate),
                                };

                var dataSet = SqlHelper.ExecuteDataset(SqlHelper.ConnectionString, Constants.StorepProcedure.VOneProfileStatisticalAnalysisLevelDepartment, sqlParameters);

                LogHelper.Instance.WriteInfo(Constants.End + methodName);
                return dataSet.Tables.Count > 0 ? dataSet.Tables[0] : null;
            }
            catch (SqlException ex)
            {
                LogHelper.Instance.WriteError(methodName + Constants.Colon + ex.Message);
                throw new Exception(ex.ToString());
            }
        }

        public DataTable GetProfileStatisticalAnalysisLevelStaff(SearchProfileNP searchProfileNP)
        {
            string methodName = MethodBase.GetCurrentMethod().DeclaringType.ToString() + Constants.Dot + MethodBase.GetCurrentMethod().Name;
            LogHelper.Instance.WriteInfo(Constants.Begin + methodName);
            try
            {
                var sqlParameters = new[]
                                {
                                    new SqlParameter(Constants.SqlParamerterName.SearchProfileNP.DepartmentId, searchProfileNP.DepartmentId),
                                    new SqlParameter(Constants.SqlParamerterName.SearchProfileNP.ReceivingFromDate, searchProfileNP.ReceivingFromDate),
                                    new SqlParameter(Constants.SqlParamerterName.SearchProfileNP.ReceivingToDate, searchProfileNP.ReceivingToDate),
                                };

                var dataSet = SqlHelper.ExecuteDataset(SqlHelper.ConnectionString, Constants.StorepProcedure.VOneProfileStatisticalAnalysisLevelStaff, sqlParameters);

                LogHelper.Instance.WriteInfo(Constants.End + methodName);
                return dataSet.Tables.Count > 0 ? dataSet.Tables[0] : null;
            }
            catch (SqlException ex)
            {
                LogHelper.Instance.WriteError(methodName + Constants.Colon + ex.Message);
                throw new Exception(ex.ToString());
            }
        }

        /// <summary>
        /// Search Profile Fees
        /// </summary>
        /// <param name="code">param Code</param>
        /// <param name="name">param Name</param>
        /// <returns>Return result search</returns>
        public IList<ProfileEN> GetProfileFeesSearch(string code, string name)
        {
            string methodName = MethodBase.GetCurrentMethod().DeclaringType.ToString() + Constants.Dot + MethodBase.GetCurrentMethod().Name;
            LogHelper.Instance.WriteInfo(Constants.Begin + methodName);
            try
            {
                var sqlParameters = new[]
                                {
                                    new SqlParameter(Constants.SqlParamerterName.Profile.Code, code),
                                    new SqlParameter(Constants.SqlParamerterName.Profile.Name, name),
                                };

                IList<ProfileEN> lstProfile = new List<ProfileEN>();
                var reader = SqlHelper.ExecuteReader(SqlHelper.ConnectionString, Constants.StorepProcedure.VOneProfileSearchFees, sqlParameters);
                while (reader.Read())
                {
                    ProfileEN profile = new ProfileEN();

                    if (!reader.IsDBNull(0))
                    {
                        profile.ProfileId = reader.GetInt32(0);
                    }

                    if (!reader.IsDBNull(1))
                    {
                        profile.ProfileStatusId = reader.GetInt32(1);
                    }

                    if (!reader.IsDBNull(2))
                    {
                        profile.ProfileTypeId = reader.GetInt32(2);
                    }

                    if (!reader.IsDBNull(3))
                    {
                        profile.LevelId = reader.GetInt16(3);
                    }

                    if (!reader.IsDBNull(4))
                    {
                        profile.StorageLocationProfileListId = reader.GetInt32(4);
                    }

                    if (!reader.IsDBNull(5))
                    {
                        profile.CreateUserId = reader.GetInt32(5);
                    }

                    if (!reader.IsDBNull(6))
                    {
                        profile.Code = reader.GetString(6);
                    }

                    if (!reader.IsDBNull(7))
                    {
                        profile.Name = reader.GetString(7);
                    }

                    if (!reader.IsDBNull(8))
                    {
                        profile.SmsGet = reader.GetString(8);
                    }

                    if (!reader.IsDBNull(9))
                    {
                        profile.Email = reader.GetString(9);
                    }

                    if (!reader.IsDBNull(10))
                    {
                        profile.CreateDate = reader.GetDateTime(10);
                    }

                    if (!reader.IsDBNull(11))
                    {
                        profile.ReceivingDate = reader.GetDateTime(11);
                    }

                    if (!reader.IsDBNull(12))
                    {
                        profile.AppointmentDate = reader.GetDateTime(12);
                    }

                    if (!reader.IsDBNull(13))
                    {
                        profile.Object = reader.GetBoolean(13);
                    }

                    if (!reader.IsDBNull(14))
                    {
                        profile.SettlementDate = reader.GetDateTime(14);
                    }

                    if (!reader.IsDBNull(15))
                    {
                        profile.Notes = reader.GetString(15);
                    }

                    if (!reader.IsDBNull(16))
                    {
                        profile.Attachments = reader.GetString(16);
                    }

                    if (!reader.IsDBNull(17))
                    {
                        profile.ContentWork = reader.GetString(17);
                    }

                    if (!reader.IsDBNull(18))
                    {
                        profile.IsDelete = reader.GetBoolean(18);
                    }

                    if (!reader.IsDBNull(19))
                    {
                        profile.OrganName = reader.GetString(19);
                    }

                    if (!reader.IsDBNull(20))
                    {
                        profile.Address = reader.GetString(20);
                    }

                    if (!reader.IsDBNull(21))
                    {
                        profile.Phone = reader.GetString(21);
                    }

                    if (!reader.IsDBNull(22))
                    {
                        profile.AttachmentsOriginal = reader.GetString(22);
                    }

                    if (!reader.IsDBNull(23))
                    {
                        profile.IsOpen = reader.GetBoolean(23);
                    }

                    lstProfile.Add(profile);
                }

                reader.Close();
                reader.Dispose();
                LogHelper.Instance.WriteInfo(Constants.End + methodName);
                return lstProfile;
            }
            catch (SqlException ex)
            {
                LogHelper.Instance.WriteError(methodName + Constants.Colon + ex.Message);
                throw new Exception(ex.ToString());
            }
        }

        /// <summary>
        /// Gets the profile by code.
        /// </summary>
        /// <param name="code">The code.</param>
        /// <returns>return ProfileEN</returns>
        public ProfileEN GetProfileByCode(string code)
        {
            string methodName = MethodBase.GetCurrentMethod().DeclaringType.ToString() + Constants.Dot + MethodBase.GetCurrentMethod().Name;
            LogHelper.Instance.WriteInfo(Constants.Begin + methodName);
            try
            {
                ProfileEN profile = null;
                var reader = SqlHelper.ExecuteReader(SqlHelper.ConnectionString, Constants.StorepProcedure.VOneProfileGetByCode, code);
                while (reader.Read())
                {
                    profile = new ProfileEN();

                    if (!reader.IsDBNull(0))
                    {
                        profile.ProfileId = reader.GetInt32(0);
                    }

                    if (!reader.IsDBNull(1))
                    {
                        profile.ProfileStatusId = reader.GetInt32(1);
                    }

                    if (!reader.IsDBNull(2))
                    {
                        profile.ProfileTypeId = reader.GetInt32(2);
                    }

                    if (!reader.IsDBNull(3))
                    {
                        profile.LevelId = reader.GetInt16(3);
                    }

                    if (!reader.IsDBNull(4))
                    {
                        profile.StorageLocationProfileListId = reader.GetInt32(4);
                    }

                    if (!reader.IsDBNull(5))
                    {
                        profile.CreateUserId = reader.GetInt32(5);
                    }

                    if (!reader.IsDBNull(6))
                    {
                        profile.Code = reader.GetString(6);
                    }

                    if (!reader.IsDBNull(7))
                    {
                        profile.Name = reader.GetString(7);
                    }

                    if (!reader.IsDBNull(8))
                    {
                        profile.SmsGet = reader.GetString(8);
                    }

                    if (!reader.IsDBNull(9))
                    {
                        profile.Email = reader.GetString(9);
                    }

                    if (!reader.IsDBNull(10))
                    {
                        profile.CreateDate = reader.GetDateTime(10);
                    }

                    if (!reader.IsDBNull(11))
                    {
                        profile.ReceivingDate = reader.GetDateTime(11);
                    }

                    if (!reader.IsDBNull(12))
                    {
                        profile.AppointmentDate = reader.GetDateTime(12);
                    }

                    if (!reader.IsDBNull(13))
                    {
                        profile.Object = reader.GetBoolean(13);
                    }

                    if (!reader.IsDBNull(14))
                    {
                        profile.SettlementDate = reader.GetDateTime(14);
                    }

                    if (!reader.IsDBNull(15))
                    {
                        profile.Notes = reader.GetString(15);
                    }

                    if (!reader.IsDBNull(16))
                    {
                        profile.Attachments = reader.GetString(16);
                    }

                    if (!reader.IsDBNull(17))
                    {
                        profile.ContentWork = reader.GetString(17);
                    }

                    if (!reader.IsDBNull(18))
                    {
                        profile.IsDelete = reader.GetBoolean(18);
                    }

                    if (!reader.IsDBNull(19))
                    {
                        profile.OrganName = reader.GetString(19);
                    }

                    if (!reader.IsDBNull(20))
                    {
                        profile.Address = reader.GetString(20);
                    }

                    if (!reader.IsDBNull(21))
                    {
                        profile.Phone = reader.GetString(21);
                    }

                    if (!reader.IsDBNull(22))
                    {
                        profile.AttachmentsOriginal = reader.GetString(22);
                    }

                    if (!reader.IsDBNull(23))
                    {
                        profile.IsOpen = reader.GetBoolean(23);
                    }
                }

                reader.Close();
                reader.Dispose();
                LogHelper.Instance.WriteInfo(Constants.End + methodName);
                return profile;
            }
            catch (SqlException ex)
            {
                LogHelper.Instance.WriteError(methodName + Constants.Colon + ex.Message);
                throw new Exception(ex.ToString());
            }
        }

        /// <summary>
        /// Gets the profile by code id.
        /// </summary>
        /// <param name="code">The code.</param>
        /// <param name="id">The id.</param>
        /// <returns>return ProfileEN</returns>
        public ProfileEN GetProfileByCodeId(string code, int id)
        {
            string methodName = MethodBase.GetCurrentMethod().DeclaringType.ToString() + Constants.Dot + MethodBase.GetCurrentMethod().Name;
            LogHelper.Instance.WriteInfo(Constants.Begin + methodName);
            try
            {
                ProfileEN profile = null;
                var reader = SqlHelper.ExecuteReader(SqlHelper.ConnectionString, Constants.StorepProcedure.VOneProfileGetByCodeId, code, id);
                while (reader.Read())
                {
                    profile = new ProfileEN();

                    if (!reader.IsDBNull(0))
                    {
                        profile.ProfileId = reader.GetInt32(0);
                    }

                    if (!reader.IsDBNull(1))
                    {
                        profile.ProfileStatusId = reader.GetInt32(1);
                    }

                    if (!reader.IsDBNull(2))
                    {
                        profile.ProfileTypeId = reader.GetInt32(2);
                    }

                    if (!reader.IsDBNull(3))
                    {
                        profile.LevelId = reader.GetInt16(3);
                    }

                    if (!reader.IsDBNull(4))
                    {
                        profile.StorageLocationProfileListId = reader.GetInt32(4);
                    }

                    if (!reader.IsDBNull(5))
                    {
                        profile.CreateUserId = reader.GetInt32(5);
                    }

                    if (!reader.IsDBNull(6))
                    {
                        profile.Code = reader.GetString(6);
                    }

                    if (!reader.IsDBNull(7))
                    {
                        profile.Name = reader.GetString(7);
                    }

                    if (!reader.IsDBNull(8))
                    {
                        profile.SmsGet = reader.GetString(8);
                    }

                    if (!reader.IsDBNull(9))
                    {
                        profile.Email = reader.GetString(9);
                    }

                    if (!reader.IsDBNull(10))
                    {
                        profile.CreateDate = reader.GetDateTime(10);
                    }

                    if (!reader.IsDBNull(11))
                    {
                        profile.ReceivingDate = reader.GetDateTime(11);
                    }

                    if (!reader.IsDBNull(12))
                    {
                        profile.AppointmentDate = reader.GetDateTime(12);
                    }

                    if (!reader.IsDBNull(13))
                    {
                        profile.Object = reader.GetBoolean(13);
                    }

                    if (!reader.IsDBNull(14))
                    {
                        profile.SettlementDate = reader.GetDateTime(14);
                    }

                    if (!reader.IsDBNull(15))
                    {
                        profile.Notes = reader.GetString(15);
                    }

                    if (!reader.IsDBNull(16))
                    {
                        profile.Attachments = reader.GetString(16);
                    }

                    if (!reader.IsDBNull(17))
                    {
                        profile.ContentWork = reader.GetString(17);
                    }

                    if (!reader.IsDBNull(18))
                    {
                        profile.IsDelete = reader.GetBoolean(18);
                    }

                    if (!reader.IsDBNull(19))
                    {
                        profile.OrganName = reader.GetString(19);
                    }

                    if (!reader.IsDBNull(20))
                    {
                        profile.Address = reader.GetString(20);
                    }

                    if (!reader.IsDBNull(21))
                    {
                        profile.Phone = reader.GetString(21);
                    }

                    if (!reader.IsDBNull(22))
                    {
                        profile.AttachmentsOriginal = reader.GetString(22);
                    }

                    if (!reader.IsDBNull(23))
                    {
                        profile.IsOpen = reader.GetBoolean(23);
                    }
                }

                reader.Close();
                reader.Dispose();
                LogHelper.Instance.WriteInfo(Constants.End + methodName);
                return profile;
            }
            catch (SqlException ex)
            {
                LogHelper.Instance.WriteError(methodName + Constants.Colon + ex.Message);
                throw new Exception(ex.ToString());
            }
        }

        public IList<ProfileEN> GetProfileStatisticalAnalysisDetails(SearchProfileNP searchProfileNP)
        {
            string methodName = MethodBase.GetCurrentMethod().DeclaringType.ToString() + Constants.Dot + MethodBase.GetCurrentMethod().Name;
            LogHelper.Instance.WriteInfo(Constants.Begin + methodName);
            try
            {
                var sqlParameters = new[]
                                {
                                    new SqlParameter(Constants.SqlParamerterName.Profile.ProfileTypeId, searchProfileNP.ProfileTypeId),
                                    new SqlParameter(Constants.SqlParamerterName.Profile.ProfileStatusCode, searchProfileNP.ProfileStatusCode),
                                    new SqlParameter(Constants.SqlParamerterName.Profile.ReceivingFromDate, searchProfileNP.ReceivingFromDate),
                                    new SqlParameter(Constants.SqlParamerterName.Profile.ReceivingToDate, searchProfileNP.ReceivingToDate),
                                };
                
                IList<ProfileEN> lstProfile = new List<ProfileEN>();
                var reader = SqlHelper.ExecuteReader(SqlHelper.ConnectionString, Constants.StorepProcedure.VOneProfileStatisticalAnalysisDetails, sqlParameters);
                while (reader.Read())
                {
                    ProfileEN profile = new ProfileEN();

                    if (!reader.IsDBNull(0))
                    {
                        profile.ProfileId = reader.GetInt32(0);
                    }

                    if (!reader.IsDBNull(1))
                    {
                        profile.ProfileStatusId = reader.GetInt32(1);
                    }

                    if (!reader.IsDBNull(2))
                    {
                        profile.ProfileTypeId = reader.GetInt32(2);
                    }

                    if (!reader.IsDBNull(3))
                    {
                        profile.LevelId = reader.GetInt16(3);
                    }

                    if (!reader.IsDBNull(4))
                    {
                        profile.StorageLocationProfileListId = reader.GetInt32(4);
                    }

                    if (!reader.IsDBNull(5))
                    {
                        profile.CreateUserId = reader.GetInt32(5);
                    }

                    if (!reader.IsDBNull(6))
                    {
                        profile.Code = reader.GetString(6);
                    }

                    if (!reader.IsDBNull(7))
                    {
                        profile.Name = reader.GetString(7);
                    }

                    if (!reader.IsDBNull(8))
                    {
                        profile.SmsGet = reader.GetString(8);
                    }

                    if (!reader.IsDBNull(9))
                    {
                        profile.Email = reader.GetString(9);
                    }

                    if (!reader.IsDBNull(10))
                    {
                        profile.CreateDate = reader.GetDateTime(10);
                    }

                    if (!reader.IsDBNull(11))
                    {
                        profile.ReceivingDate = reader.GetDateTime(11);
                    }

                    if (!reader.IsDBNull(12))
                    {
                        profile.AppointmentDate = reader.GetDateTime(12);
                    }

                    if (!reader.IsDBNull(13))
                    {
                        profile.Object = reader.GetBoolean(13);
                    }

                    if (!reader.IsDBNull(14))
                    {
                        profile.SettlementDate = reader.GetDateTime(14);
                    }

                    if (!reader.IsDBNull(15))
                    {
                        profile.Notes = reader.GetString(15);
                    }

                    if (!reader.IsDBNull(16))
                    {
                        profile.Attachments = reader.GetString(16);
                    }

                    if (!reader.IsDBNull(17))
                    {
                        profile.ContentWork = reader.GetString(17);
                    }

                    if (!reader.IsDBNull(18))
                    {
                        profile.IsDelete = reader.GetBoolean(18);
                    }

                    if (!reader.IsDBNull(19))
                    {
                        profile.OrganName = reader.GetString(19);
                    }

                    if (!reader.IsDBNull(20))
                    {
                        profile.Address = reader.GetString(20);
                    }

                    if (!reader.IsDBNull(21))
                    {
                        profile.Phone = reader.GetString(21);
                    }

                    if (!reader.IsDBNull(22))
                    {
                        profile.AttachmentsOriginal = reader.GetString(22);
                    }

                    if (!reader.IsDBNull(23))
                    {
                        profile.IsOpen = reader.GetBoolean(23);
                    }

                    lstProfile.Add(profile);
                }

                reader.Close();
                reader.Dispose();
                LogHelper.Instance.WriteInfo(Constants.End + methodName);
                return lstProfile;
            }
            catch (SqlException ex)
            {
                LogHelper.Instance.WriteError(methodName + Constants.Colon + ex.Message);
                throw new Exception(ex.ToString());
            }
        }

        public IList<ProfileEN> GetProfileStatisticalAnalysisLevelDepartmentDetails(SearchProfileNP searchProfileNP)
        {
            string methodName = MethodBase.GetCurrentMethod().DeclaringType.ToString() + Constants.Dot + MethodBase.GetCurrentMethod().Name;
            LogHelper.Instance.WriteInfo(Constants.Begin + methodName);
            try
            {
                var sqlParameters = new[]
                                {
                                    new SqlParameter(Constants.SqlParamerterName.SearchProfileNP.DepartmentId, searchProfileNP.DepartmentId),
                                    new SqlParameter(Constants.SqlParamerterName.SearchProfileNP.ProfileStatusCode, searchProfileNP.ProfileStatusCode),
                                    new SqlParameter(Constants.SqlParamerterName.SearchProfileNP.ReceivingFromDate, searchProfileNP.ReceivingFromDate),
                                    new SqlParameter(Constants.SqlParamerterName.SearchProfileNP.ReceivingToDate, searchProfileNP.ReceivingToDate),
                                };

                IList<ProfileEN> lstProfile = new List<ProfileEN>();
                var reader = SqlHelper.ExecuteReader(SqlHelper.ConnectionString, Constants.StorepProcedure.VOneProfileStatisticalAnalysisLevelDepartmentDetails, sqlParameters);
                while (reader.Read())
                {
                    ProfileEN profile = new ProfileEN();

                    if (!reader.IsDBNull(0))
                    {
                        profile.ProfileId = reader.GetInt32(0);
                    }

                    if (!reader.IsDBNull(1))
                    {
                        profile.ProfileStatusId = reader.GetInt32(1);
                    }

                    if (!reader.IsDBNull(2))
                    {
                        profile.ProfileTypeId = reader.GetInt32(2);
                    }

                    if (!reader.IsDBNull(3))
                    {
                        profile.LevelId = reader.GetInt16(3);
                    }

                    if (!reader.IsDBNull(4))
                    {
                        profile.StorageLocationProfileListId = reader.GetInt32(4);
                    }

                    if (!reader.IsDBNull(5))
                    {
                        profile.CreateUserId = reader.GetInt32(5);
                    }

                    if (!reader.IsDBNull(6))
                    {
                        profile.Code = reader.GetString(6);
                    }

                    if (!reader.IsDBNull(7))
                    {
                        profile.Name = reader.GetString(7);
                    }

                    if (!reader.IsDBNull(8))
                    {
                        profile.SmsGet = reader.GetString(8);
                    }

                    if (!reader.IsDBNull(9))
                    {
                        profile.Email = reader.GetString(9);
                    }

                    if (!reader.IsDBNull(10))
                    {
                        profile.CreateDate = reader.GetDateTime(10);
                    }

                    if (!reader.IsDBNull(11))
                    {
                        profile.ReceivingDate = reader.GetDateTime(11);
                    }

                    if (!reader.IsDBNull(12))
                    {
                        profile.AppointmentDate = reader.GetDateTime(12);
                    }

                    if (!reader.IsDBNull(13))
                    {
                        profile.Object = reader.GetBoolean(13);
                    }

                    if (!reader.IsDBNull(14))
                    {
                        profile.SettlementDate = reader.GetDateTime(14);
                    }

                    if (!reader.IsDBNull(15))
                    {
                        profile.Notes = reader.GetString(15);
                    }

                    if (!reader.IsDBNull(16))
                    {
                        profile.Attachments = reader.GetString(16);
                    }

                    if (!reader.IsDBNull(17))
                    {
                        profile.ContentWork = reader.GetString(17);
                    }

                    if (!reader.IsDBNull(18))
                    {
                        profile.IsDelete = reader.GetBoolean(18);
                    }

                    if (!reader.IsDBNull(19))
                    {
                        profile.OrganName = reader.GetString(19);
                    }

                    if (!reader.IsDBNull(20))
                    {
                        profile.Address = reader.GetString(20);
                    }

                    if (!reader.IsDBNull(21))
                    {
                        profile.Phone = reader.GetString(21);
                    }

                    if (!reader.IsDBNull(22))
                    {
                        profile.AttachmentsOriginal = reader.GetString(22);
                    }

                    if (!reader.IsDBNull(23))
                    {
                        profile.IsOpen = reader.GetBoolean(23);
                    }

                    lstProfile.Add(profile);
                }

                reader.Close();
                reader.Dispose();
                LogHelper.Instance.WriteInfo(Constants.End + methodName);
                return lstProfile;
            }
            catch (SqlException ex)
            {
                LogHelper.Instance.WriteError(methodName + Constants.Colon + ex.Message);
                throw new Exception(ex.ToString());
            }
        }

        public IList<ProfileEN> GetProfileStatisticalAnalysisLevelStaffDetails(SearchProfileNP searchProfileNP)
        {
            string methodName = MethodBase.GetCurrentMethod().DeclaringType.ToString() + Constants.Dot + MethodBase.GetCurrentMethod().Name;
            LogHelper.Instance.WriteInfo(Constants.Begin + methodName);
            try
            {
                var sqlParameters = new[]
                                {
                                    new SqlParameter(Constants.SqlParamerterName.SearchProfileNP.UserId, searchProfileNP.UserId),
                                    new SqlParameter(Constants.SqlParamerterName.SearchProfileNP.DepartmentId, searchProfileNP.DepartmentId),
                                    new SqlParameter(Constants.SqlParamerterName.SearchProfileNP.ProfileStatusCode, searchProfileNP.ProfileStatusCode),
                                    new SqlParameter(Constants.SqlParamerterName.SearchProfileNP.ReceivingFromDate, searchProfileNP.ReceivingFromDate),
                                    new SqlParameter(Constants.SqlParamerterName.SearchProfileNP.ReceivingToDate, searchProfileNP.ReceivingToDate),
                                };

                IList<ProfileEN> lstProfile = new List<ProfileEN>();
                var reader = SqlHelper.ExecuteReader(SqlHelper.ConnectionString, Constants.StorepProcedure.VOneProfileStatisticalAnalysisLevelStaffDetails, sqlParameters);
                while (reader.Read())
                {
                    ProfileEN profile = new ProfileEN();

                    if (!reader.IsDBNull(0))
                    {
                        profile.ProfileId = reader.GetInt32(0);
                    }

                    if (!reader.IsDBNull(1))
                    {
                        profile.ProfileStatusId = reader.GetInt32(1);
                    }

                    if (!reader.IsDBNull(2))
                    {
                        profile.ProfileTypeId = reader.GetInt32(2);
                    }

                    if (!reader.IsDBNull(3))
                    {
                        profile.LevelId = reader.GetInt16(3);
                    }

                    if (!reader.IsDBNull(4))
                    {
                        profile.StorageLocationProfileListId = reader.GetInt32(4);
                    }

                    if (!reader.IsDBNull(5))
                    {
                        profile.CreateUserId = reader.GetInt32(5);
                    }

                    if (!reader.IsDBNull(6))
                    {
                        profile.Code = reader.GetString(6);
                    }

                    if (!reader.IsDBNull(7))
                    {
                        profile.Name = reader.GetString(7);
                    }

                    if (!reader.IsDBNull(8))
                    {
                        profile.SmsGet = reader.GetString(8);
                    }

                    if (!reader.IsDBNull(9))
                    {
                        profile.Email = reader.GetString(9);
                    }

                    if (!reader.IsDBNull(10))
                    {
                        profile.CreateDate = reader.GetDateTime(10);
                    }

                    if (!reader.IsDBNull(11))
                    {
                        profile.ReceivingDate = reader.GetDateTime(11);
                    }

                    if (!reader.IsDBNull(12))
                    {
                        profile.AppointmentDate = reader.GetDateTime(12);
                    }

                    if (!reader.IsDBNull(13))
                    {
                        profile.Object = reader.GetBoolean(13);
                    }

                    if (!reader.IsDBNull(14))
                    {
                        profile.SettlementDate = reader.GetDateTime(14);
                    }

                    if (!reader.IsDBNull(15))
                    {
                        profile.Notes = reader.GetString(15);
                    }

                    if (!reader.IsDBNull(16))
                    {
                        profile.Attachments = reader.GetString(16);
                    }

                    if (!reader.IsDBNull(17))
                    {
                        profile.ContentWork = reader.GetString(17);
                    }

                    if (!reader.IsDBNull(18))
                    {
                        profile.IsDelete = reader.GetBoolean(18);
                    }

                    if (!reader.IsDBNull(19))
                    {
                        profile.OrganName = reader.GetString(19);
                    }

                    if (!reader.IsDBNull(20))
                    {
                        profile.Address = reader.GetString(20);
                    }

                    if (!reader.IsDBNull(21))
                    {
                        profile.Phone = reader.GetString(21);
                    }

                    if (!reader.IsDBNull(22))
                    {
                        profile.AttachmentsOriginal = reader.GetString(22);
                    }

                    if (!reader.IsDBNull(23))
                    {
                        profile.IsOpen = reader.GetBoolean(23);
                    }

                    lstProfile.Add(profile);
                }

                reader.Close();
                reader.Dispose();
                LogHelper.Instance.WriteInfo(Constants.End + methodName);
                return lstProfile;
            }
            catch (SqlException ex)
            {
                LogHelper.Instance.WriteError(methodName + Constants.Colon + ex.Message);
                throw new Exception(ex.ToString());
            }
        }
    }
}